package com.ruoyi.common.utils.excl.workbook;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;

/**
 * Workbook 类工具
 * <p>
 * 1-对生成的 Workbook 对象进行一些自定义操作的抽离
 * </p>
 *
 * @author HHKJ-SERVER-ZJ 2022/4/13 15:47
 */
@Slf4j
public class WorkbookUtils {

    /**
     * 给 workbook 部分字段添加下拉列表内容
     *
     * @param workbook      需要操作的 excel对应的workbook对象
     * @param firstRow      开始行号
     * @param col           第几列
     * @param selectedArray 选项列表
     */
    public static void selectHiddenList(Workbook workbook, String[] selectedArray, int firstRow, int col, String sheetName) {
        /*
         * firstRow 开始行号(下标0开始)
         * lastRow  结束行号，最大65535
         * firstCol 区域中第一个单元格的列号 (下标0开始)
         * lastCol 区域中最后一个单元格的列号
         * dataArray 下拉内容
         * sheetHidden 影藏的sheet编号（例如1,2,3），多个下拉数据不能使用同一个
         */

        // String[] dataArray = colName.toArray(new String[0]);
        Sheet hidden = workbook.createSheet(sheetName);
        Cell cell;
        for (int i = 0, length = selectedArray.length; i < length; i++) {
            String name = selectedArray[i];
            Row row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }

        Name namedCell = workbook.createName();
        namedCell.setNameName(sheetName);
        namedCell.setRefersToFormula(sheetName + "!$A$1:$A$" + selectedArray.length);
        //加载数据,将名称为hidden的
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(sheetName);

        Sheet sheet = workbook.getSheetAt(0);
        //  生成下拉列表
        //  只对(x，x)单元格有效
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRow, 65535, col, col);
        //  生成下拉框内容
        // DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(selectedArray);
        HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, constraint);
        //  对sheet页生效
        sheet.addValidationData(dataValidation);
    }

    /**
     * 给excel添加下拉选框信息
     *
     * @param workbook excel文件
     * @param startRow 开始行
     * @param col      列
     * @param data     数据
     */
    public static void selectList(Workbook workbook, int startRow, int col, String[] data) {
        selectList(0, workbook, startRow, col, data);
    }

    /**
     * 给excel添加下拉选框信息
     *
     * @param workbook excel文件
     * @param startRow 开始行
     * @param col      列
     * @param data     数据
     */
    public static void selectList(int sheetIndex, Workbook workbook, int startRow, int col, String[] data) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        //生成下拉列表
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(startRow, 90000, col, col);
        //生成下拉框内容
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();
        DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(data);
        DataValidation validation = dvHelper.createValidation(dvConstraint, cellRangeAddressList);
        //设置错误信息提示
        validation.setShowErrorBox(true);
        //对sheet页生效
        sheet.addValidationData(validation);
    }

    /**
     * 从文件中获取workbook
     *
     * @param file 文件
     * @return Workbook
     */
    public static Workbook getWorkbookFromMultipartFile(MultipartFile file) {
        try {
            return new XSSFWorkbook(file.getInputStream());
        } catch (IOException e) {
            log.error("excel文件读取失败");
        }
        return new XSSFWorkbook();
    }

    /**
     * 给 workbook 部分字段添加下拉列表内容
     *
     * @param workbook        需要操作的 excel对应的workbook对象
     * @param firstCol        开始列号
     * @param lastCol         结束列号
     * @param selectedArray   选项列表
     * @param hiddenSheetName 下拉选框内容里列表
     */
    public static void selectHiddenList(Workbook workbook, int firstCol, int lastCol, String[] selectedArray, String hiddenSheetName) {
        Sheet hidden = workbook.createSheet(hiddenSheetName);
        Cell cell;
        for (int i = 0, length = selectedArray.length; i < length; i++) {
            String name = selectedArray[i];
            Row row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }

        Name namedCell = workbook.createName();
        namedCell.setNameName(hiddenSheetName);
        // 单元格判断，如果下拉选框内容为空的话 直接指定第一个单元格数据对应下拉选
        namedCell.setRefersToFormula(hiddenSheetName + "!$A$1:$A$" + (Math.max(selectedArray.length, 1)));
        //加载数据,将名称为hidden的
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenSheetName);

        Sheet sheet = workbook.getSheetAt(0);
        //  生成下拉列表
        //  只对(x，x)单元格有效
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(2, 65535, firstCol, lastCol);
        //  生成下拉框内容
        // DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(selectedArray);
        HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, constraint);
        //  对sheet页生效
        sheet.addValidationData(dataValidation);
    }

    /**
     * 给 workbook 部分字段添加下拉列表内容
     *
     * @param workbook      需要操作的 excel对应的workbook对象
     * @param firstCol      开始列号
     * @param selectedArray 选项列表
     */
    public static void selectHiddenList(Workbook workbook, int firstCol, String[] selectedArray) {
        selectHiddenList(workbook, firstCol, firstCol, selectedArray);
    }

    /**
     * 给 workbook 部分字段添加下拉列表内容
     *
     * @param workbook      需要操作的 excel对应的workbook对象
     * @param firstCol      开始列号
     * @param lastCol       结束列号
     * @param selectedArray 选项列表
     */
    public static void selectHiddenList(Workbook workbook, int firstCol, int lastCol, String[] selectedArray) {
        /*
         * firstRow 开始行号(下标0开始)
         * lastRow  结束行号，最大65535
         * firstCol 区域中第一个单元格的列号 (下标0开始)
         * lastCol 区域中最后一个单元格的列号
         * dataArray 下拉内容
         * sheetHidden 影藏的sheet编号（例如1,2,3），多个下拉数据不能使用同一个
         */

        // String[] dataArray = colName.toArray(new String[0]);
        Sheet hidden = workbook.createSheet("hidden" + firstCol);
        Cell cell;
        for (int i = 0, length = selectedArray.length; i < length; i++) {
            String name = selectedArray[i];
            Row row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }

        Name namedCell = workbook.createName();
        namedCell.setNameName("hidden" + firstCol);
        namedCell.setRefersToFormula("hidden!$A$1:$A$" + selectedArray.length);
        //加载数据,将名称为hidden的
        DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden" + firstCol);

        Sheet sheet = workbook.getSheetAt(0);
        //  生成下拉列表
        //  只对(x，x)单元格有效
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(2, 65535, firstCol, lastCol);
        //  生成下拉框内容
        // DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(selectedArray);
        HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, constraint);
        //  对sheet页生效
        sheet.addValidationData(dataValidation);
    }

    public static void setModelTitleStyle(Workbook workbook) {
        // 获取第一个单元格
        Sheet sheetAt = workbook.getSheetAt(0);
        Row row = sheetAt.getRow(0);
        Cell cell = row.getCell(0);

        // 创建单元格样式 设置折行
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setWrapText(true);

        // 设置行高 设置样式
        row.setHeight((short) 2200);
        cell.setCellStyle(cellStyle);

        // 创建设置字体
        Font font = workbook.createFont();
        // 设置字体高度 - 以1pt为单位, 设置字体为60pt
        font.setFontHeightInPoints((short) 20);
        // 加粗
        font.setBold(true);
        // 设置字体颜色为深红色
        font.setColor(Font.COLOR_RED);

        // 获取title单元格富文本编辑器 设置字体
        RichTextString titleRichText = cell.getRichStringCellValue();
        String titleContent = titleRichText.getString();
        titleRichText.applyFont(0, titleContent.lastIndexOf("模板") + 2, font);

        // 设置单元格的值
        cell.setCellValue(titleRichText);
    }

}
